package org.sanguine.tests.protei.springapp.mvc.dao;
import org.sanguine.tests.protei.springapp.mvc.models.Entity;
import org.sanguine.tests.protei.springapp.mvc.models.annotations.Primary;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.logging.Logger;

/**
 * Created by alex on 27.02.14.
 * Author: Alex Kuznetsov
 */

/**
 * Bean для работы со схемой базы данных MySQL
 */

@Component
@Scope("prototype")
public class MySQLSchemaBean implements SchemaBean {
    private final static String sqlExpr = "USE %s; " +
                                            "DROP TABLE IF EXISTS %s; " +
                                            "CREATE TABLE IF NOT EXISTS %s (%s);";
    private final static String keyExpr = "%s INTEGER AUTO_INCREMENT PRIMARY KEY";

    private String tableName;
    private String sequenceName;
    private Class<?> entityClass;
    private Map<String, String> sqlColumns = new LinkedHashMap<String, String>();
    private Map<String, Class<?>> entityFieldsTypes = new LinkedHashMap<String, Class<?>>();
    private Map<String, Map<String, Object>> entityGettersSetters = new LinkedHashMap<String, Map<String, Object>>();
    private String columnsDefinition;
    private String columnsInsertClauseHead;
    private String keyName;
    private Class<?> keyClass;
    private Field keyAccessor;
    private Method keySetter;
    private Method keyGetter;
    private String databaseName;

    public static class SQLTypeMap {
        private static Map<String, Integer> mapJavaTypesToSQL = new HashMap<String, Integer>();
        private static Map<Integer, String> mapSQLTypeToHisName = new HashMap<Integer, String>();


        /**
         * Translates a data type from an integer (java.sql.Types value) to a string
         * that represents the corresponding class.
         *
         * @param type The java.sql.Types value to convert to a string representation.
         * @return The class name that corresponds to the given java.sql.Types value,
         *         or "java.lang.Object" if the type has no known mapping.
         */
        public static String convert( int type ) {
//            String result = "java.lang.Object";

            String result = null;

            switch( type ) {
                case Types.CHAR:
                case Types.VARCHAR:
                case Types.LONGVARCHAR:
                    result = "java.lang.String";
                    break;

                case Types.NUMERIC:
                case Types.DECIMAL:
                    result = "java.math.BigDecimal";
                    break;

                case Types.BIT:
                    result = "java.lang.Boolean";
                    break;

                case Types.TINYINT:
                    result = "java.lang.Byte";
                    break;

                case Types.SMALLINT:
                    result = "java.lang.Short";
                    break;

                case Types.INTEGER:
                    result = "java.lang.Integer";
                    break;

                case Types.BIGINT:
                    result = "java.lang.Long";
                    break;

                case Types.REAL:
                    result = "java.lang.Real";
                    break;

                case Types.FLOAT:
                case Types.DOUBLE:
                    result = "java.lang.Double";
                    break;

                case Types.BINARY:
                case Types.VARBINARY:
                case Types.LONGVARBINARY:
                    result = "java.lang.Byte[]";
                    break;

                case Types.DATE:
                    result = "java.sql.Date";
                    break;

                case Types.TIME:
                    result = "java.sql.Time";
                    break;

                case Types.TIMESTAMP:
                    result = "java.sql.Timestamp";
                    break;
            }

            return result;
        }

        public static int convert(String className) {
            if (mapJavaTypesToSQL.size() == 0) {
                fillMapTypes();
            }
            Integer type = mapJavaTypesToSQL.get(className);
            return (type == null) ? -1 : type;

        }

        public static String JavaTypeToSQL(String className) {
            int sqlType = convert(className);
            return mapSQLTypeToHisName.get(sqlType);
        }

        private static void fillMapTypes() {
            for (Field t : java.sql.Types.class.getFields()) {
                try {
                    int type = t.getInt(java.sql.Types.class);
                    if (type != Types.JAVA_OBJECT) {
                        mapJavaTypesToSQL.put(convert(type), type);
                        mapSQLTypeToHisName.put(type, t.getName());
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
            mapJavaTypesToSQL.put("java.lang.String", Types.VARCHAR);
            mapSQLTypeToHisName.put(Types.VARCHAR, "VARCHAR(255)");
            mapSQLTypeToHisName.put(Types.DECIMAL, "DECIMAL(10,2)");
        }
    }

    @Override
    public String getDDLExpression() {
        if (entityClass == null || columnsDefinition == null) return null;
        String expr = sqlExpr.format(sqlExpr, databaseName, tableName, tableName, columnsDefinition, sequenceName);
        return expr;
    }

    private void guessStructureAndBuildColumnsStatement() {
        guessEntityStructure();
        createColumnsDefinition();
    }

    private void createColumnsDefinition() {
        columnsDefinition = null;
        columnsInsertClauseHead = null;
        StringBuffer defBuffer = new StringBuffer();
        StringBuffer clauseBuffer = new StringBuffer("(");
        if (keyName != null && !keyName.isEmpty()) {
            defBuffer.append(keyExpr.format(keyExpr, keyName));
        } else {
            Logger.getLogger(getClass().getName()).severe("Entity class " + entityClass.getName() + " don't have primary key field");
            return;
        }
        for (Map.Entry<String, String> column : sqlColumns.entrySet()) {
            String sqlType = column.getValue();
            if (sqlType != null) {
                defBuffer.append(", ");
                defBuffer.append(column.getKey() + " " + sqlType);
                clauseBuffer.append(column.getKey());
                clauseBuffer.append(", ");
            } else Logger.getLogger(getClass().getName()).severe("Skip column definition for " + column.getKey() + " because javaType-to-SQLType mapping failure");
        }
        clauseBuffer.delete(clauseBuffer.length() - 2, clauseBuffer.length());
        clauseBuffer.append(")");

        columnsDefinition = defBuffer.toString();
        columnsInsertClauseHead = clauseBuffer.toString();
    }

    private void guessEntityStructure() {
        keyName = null;
        keyClass = null;
        keySetter = null;
        keyAccessor = null;
        keyGetter = null;
        sqlColumns.clear();
        entityFieldsTypes.clear();
        entityGettersSetters.clear();
        //Guess public fields as properties
        for (Field field : entityClass.getFields()) {
            if (keyAccessor == null && field.isAnnotationPresent(Primary.class)) {
                keyName = field.getName();
                keyClass = field.getType();
                keyAccessor = field;
                continue;
            }
            sqlColumns.put(field.getName(), getSQLType(field.getType()));
            entityFieldsTypes.put(field.getName(), field.getType());
            Map<String, Object> accessor = new HashMap<String, Object>();
            accessor.put("field", field);
            entityGettersSetters.put(field.getName(), accessor);
        }
        //Guess setters/getters as properties
        for (Method method : entityClass.getMethods()) {
            if (keyAccessor == null && (keyGetter == null || keySetter == null) && method.isAnnotationPresent(Primary.class)) {
                keyName = nameFromMethod(method.getName());
                Class<?> type = method.getReturnType();
                if (type.getName().equals("void")) {
                    keySetter = method;
                } else {
                    keyGetter = method;
                    keyClass = type;
                }
                continue;
            }
            if (isSetterMethod(method)) {
                String name = nameFromMethod(method.getName());
                Map<String, Object> accessor = entityGettersSetters.get(name);
                if (accessor != null && accessor.get("field") != null) continue;
                if (accessor == null) accessor = new HashMap<String, Object>();
                accessor.put("setter", method);
                entityGettersSetters.put(name, accessor);
                continue;
            }
            if (isGetterMethod(method)) {
                String name = nameFromMethod(method.getName());
                if (name.equals("class")) continue;
                Map<String, Object> accessor = entityGettersSetters.get(name);
                if (accessor != null && accessor.get("field") != null) continue;
                if (accessor == null) accessor = new HashMap<String, Object>();
                accessor.put("getter", method);
                entityGettersSetters.put(name, accessor);
                sqlColumns.put(name, getSQLType(method.getReturnType()));
                entityFieldsTypes.put(name, method.getReturnType());
            }
        }

    }

    private String nameFromMethod(String methodName) {
        String name;
        if (methodName.startsWith("get") || methodName.startsWith("set")) name = methodName.substring(3, methodName.length());
        else name = methodName;
        name = name.substring(0,1).toLowerCase().concat(name.substring(1));
        return name;
    }

    private boolean isGetterMethod(Method method) {
        String name = method.getName();
        return name.startsWith("get");
    }

    private boolean isSetterMethod(Method method) {
        String name = method.getName();
        return name.startsWith("set");
    }

    private String getSQLType(Class<?> clazz) {
        String javaType = clazz.getCanonicalName();
        return SQLTypeMap.JavaTypeToSQL(javaType);
    }

    @Override
    public void setEntityClass(Class<? extends Entity> clazz) {
        this.entityClass = clazz;
        if (entityClass != null) {
            guessStructureAndBuildColumnsStatement();
            tableName = entityClass.getSimpleName();
            sequenceName = "SEQ_" + tableName;
        } else {
            tableName = null;
            sequenceName = null;
            columnsDefinition = null;
            keyName = null;
            sqlColumns.clear();
        }
    }

    @Override
    public String getColumnsClause(Entity entity, boolean update) {
        if (update) return createUpdateClause(entity);
        else return createInsertClause(entity);
    }

    private String createUpdateClause(Entity entity) {
        StringBuffer values = new StringBuffer();
        for (Map.Entry<String, Class<?>> column : entityFieldsTypes.entrySet()) {
            Class<?> type = column.getValue();
            if (type != null) {
                String value = getStringPresentationOfProperty(entity, column.getKey(), type);
                if (value == null) Logger.getLogger(getClass().getName()).severe("Skip column value for " + column.getKey() + " because field not found");
                else {
                    values.append(column.getKey());
                    values.append(" = ");
                    values.append(value);
                    values.append(", ");
                }
            } else Logger.getLogger(getClass().getName()).severe("Skip column value for " + column.getKey() + " because field type isn't defined");
        }
        values.delete(values.length()-2, values.length());
        if (keyName != null && !keyName.isEmpty()) {
            values.append(" WHERE ");
            values.append(String.format("%s = %s", keyName, getStringPresentationOfKey(entity)));
        } else {
            Logger.getLogger(getClass().getName()).severe("Entity class " + entityClass.getName() + " don't have primary key field");
            return null;
        }
        return values.toString();
    }

    private String createInsertClause(Entity entity) {
        if (columnsInsertClauseHead == null) return null;
        StringBuffer values = new StringBuffer();
        if (keyName != null && !keyName.isEmpty()) {
            values.append("(");
        } else {
            Logger.getLogger(getClass().getName()).severe("Entity class " + entityClass.getName() + " don't have primary key field");
            return null;
        }
        for (Map.Entry<String, Class<?>> column : entityFieldsTypes.entrySet()) {
            Class<?> type = column.getValue();
            if (type != null) {
                String value = getStringPresentationOfProperty(entity, column.getKey(), type);
                if (value == null) Logger.getLogger(getClass().getName()).severe("Skip column value for " + column.getKey() + " because field not found");
                else {
                    values.append(value);
                    values.append(", ");
                }
            } else Logger.getLogger(getClass().getName()).severe("Skip column value for " + column.getKey() + " because field type isn't defined");
        }
        values.delete(values.length() - 2, values.length());
        values.append(")");

        return columnsInsertClauseHead + " VALUES " + values.toString();
    }

    private String getStringPresentationOfKey(Entity entity) {
        Field field = keyAccessor;
        if (field != null) {
            try {
                return "'" + (keyClass.cast(field.get(entity))).toString() + "'";
            } catch (IllegalAccessException e) {
                e.printStackTrace();
                return null;
            }
        }
        Method method = keyGetter;
        if (method != null) {
            try {
                Object obj = method.invoke(entity);
                return "'" + (keyClass.cast(obj)).toString() + "'";
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    private String getStringPresentationOfProperty(Entity entity, String propertyName, Class<?> type) {
        Map<String, Object> accessor = entityGettersSetters.get(propertyName);
        if (accessor == null) return null;
        Field field = (Field)accessor.get("field");
        if (field != null) {
            try {
                return "'" + (type.cast(field.get(entity))).toString() + "'";
            } catch (IllegalAccessException e) {
                e.printStackTrace();
                return null;
            }
        }
        Method method = (Method)accessor.get("getter");
        if (method != null) {
            try {
                Object obj = method.invoke(entity);
                return "'" + (type.cast(obj)).toString() + "'";
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public String getKeyName() {
        return keyName;
    }

    @Override
    public Object createEntityInstance(Object resultSet) {
        ResultSet result = (ResultSet)resultSet;
        if (result == null) return null;
        Object newEntity = null;
        try {
            newEntity = entityClass.newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
            return null;
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            return null;
        }
        if (keyName == null || keyName.isEmpty() || keyClass == null) {
            Logger.getLogger(getClass().getName()).severe("Key's name not defined");
            return null;
        }
        try {
            setValueOfKey(newEntity, result.getString(keyName));
        } catch (SQLException e) {
            Logger.getLogger(getClass().getName()).severe("Skip column value for " + keyName + " because wrapping object and database schema are different");
            return null;
        }
        for (Map.Entry<String, Class<?>> column : entityFieldsTypes.entrySet()) {
            Class<?> type = column.getValue();
            String name = column.getKey();
            if (type != null) {
                try {
                    setValueOfProperty(newEntity, name, type, result.getString(name));
                } catch (SQLException e) {
                    Logger.getLogger(getClass().getName()).severe("Skip column value for " + name + " because wrapping object and database schema are different");
                    return null;
                }
            } else {
                Logger.getLogger(getClass().getName()).severe("Skip column value for " + name + " because field type isn't defined");
                return null;
            }
        }
        return newEntity;
    }

    private void setValueOfKey(Object entity, String value) {

        Field f = keyAccessor;
        Object obj = convert(keyClass, value);
        if (obj == null) {
            Logger.getLogger(getClass().getName()).severe("New target object of " + keyClass + " for value " + value + " is null");
            return;
        }
        if (f != null) {
            try {
                f.set(entity, obj);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            return;
        }
        Method method = keySetter;
        if (method != null) {
            try {
                method.invoke(entity, obj);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
    }

    private Object convert(Class<?> targetType, String text) {
/*
        PropertyEditor editor = PropertyEditorManager.findEditor(targetType);
        if (editor == null) {
            Logger.getLogger(getClass().getName()).warning("Can't convert to type " + targetType.getName());
            return null;
        }
        editor.setAsText(text);
        Object obj = editor.getValue();
        return obj;
*/

        Object obj = null;
        try {
            Constructor constructor = targetType.getConstructor(new Class[]{String.class});
            obj=constructor.newInstance(text);
        } catch (SecurityException e) {
            Logger.getLogger(getClass().getName()).warning("Can't convert to type " + targetType.getName());
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            Logger.getLogger(getClass().getName()).warning("Can't convert to type " + targetType.getName());
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            Logger.getLogger(getClass().getName()).warning("Can't convert to type " + targetType.getName());
            e.printStackTrace();
        } catch (InstantiationException e) {
            Logger.getLogger(getClass().getName()).warning("Can't convert to type " + targetType.getName());
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            Logger.getLogger(getClass().getName()).warning("Can't convert to type " + targetType.getName());
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            Logger.getLogger(getClass().getName()).warning("Can't convert to type " + targetType.getName());
            e.printStackTrace();
        }
        return obj;
    }

    private void setValueOfProperty(Object entity, String propertyName, Class<?> typeArg, String value) {
        Map<String, Object> accessor = entityGettersSetters.get(propertyName);
        if (accessor == null) return;
        Field f = (Field)accessor.get("field");
        Object obj = convert(typeArg, value);
        if (obj == null) {
            Logger.getLogger(getClass().getName()).warning("New target object of " + typeArg + " for value " + value + " is null");
        }
        if (f != null) {
            try {
                f.set(entity, obj);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            return;
        }
        Method method = (Method)accessor.get("setter");
        if (method != null) {
            try {
                method.invoke(entity, obj);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void setDatabaseName(String database) {
        this.databaseName = database;
    }

    @Override
    public Object getNativeKeyValue(String key) {
        Integer nativeKey = null;
        try {
            nativeKey = new Integer(key);
        } catch (NumberFormatException e) {
            Logger.getLogger(getClass().getName()).severe("Cannot convert key= " + key + " to native key");
        }
        return nativeKey;
    }

    @Override
    public String nativeKeyToString(Object key) {

        if (key instanceof Number) return key.toString();
        Logger.getLogger(getClass().getName()).severe("Cannot convert native key = " + key + " to application level key");
        return null;
    }
}
